package utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class Getter {

	public int getPageId(String str, Connection conn) {
		JSONObject jo = JSONObject.fromObject(str);
		String url = (String) jo.get("local");
		return getId(url, conn);
	}

	private int getId(String url, Connection conn) {
		int id = -1;
		try {
			String sql = "select idPage from Page where url = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, url);
			ResultSet r = ps.executeQuery();
			if (r.next()) {
				id = r.getInt("idPage");
				ps.close();
				return id;
			} else {
				if (this.insert(url, conn))
					id = this.getId(url, conn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}

	private boolean insert(String url, Connection conn) {
		try {
			String sql = "insert into Page(url) values(?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, url);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public JSONArray getMarkers(String ip, int id, Connection conn) {
		JSONArray markers = new JSONArray();
		try {
			String sql = "select txt,tp,v from marker where idPage = " + id
					+ " and ip='" + ip + "'";
			Statement ps = conn.createStatement();
			ResultSet r = ps.executeQuery(sql);
			while (r.next()) {
				JSONObject marker = new JSONObject();
				marker.element("txt", r.getString("txt"));
				marker.element("tp", r.getString("tp"));
				marker.element("v", r.getString("v"));
				markers.add(marker);
			}
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return markers;
	}

	public JSONObject getComments(String ip, int id, Connection conn) {
		JSONObject comments = new JSONObject();
		try {
			String sql = "select cmnt,id,usr,time from comment where idPage = "
					+ id + " and usr='" + ip + "'";
			Statement ps = conn.createStatement();
			ResultSet r = ps.executeQuery(sql);
			while (r.next()) {
				JSONObject comment = new JSONObject();
				int index = r.getInt("id");
				comment.element("cmnt", r.getString("cmnt"));
				comment.element("id", index);
				comment.element("usr", ip);
				comment.element("time", r.getString("time"));
				comments.element("" + index, comment);
			}
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return comments;
	}
}
